<?php
class ModelSaleLuckydraw extends Model {
  public function addLuckydraw($data) {
    $this->db->query("INSERT INTO " . DB_PREFIX . "luckydraw SET luckydraw_name = '" . $this->db->escape($data['luckydraw_name']) . "', start_time = '" . $this->db->escape($data['start_time']) . "', end_time = '" . $this->db->escape($data['end_time']) . "', chance = '" . (float)$data['chance'] . "', status = '" . (int)$data['status'] . "', date_added = NOW()");
    $luckydraw_id = $this->db->getLastId();
    if (isset($data['products'])) {
      $this->db->query("DELETE FROM " . DB_PREFIX . "luckydraw_product WHERE luckydraw_id = '" . (int)$luckydraw_id . "'");
      foreach ($data['products'] as $product) {
        $product['luckydraw_id'] = $luckydraw_id;
        M('luckydraw_product')->data($product)->add();
      }
    }
    if (isset($data['numbers'])) {
      $this->db->query("DELETE FROM " . DB_PREFIX . "luckydraw_number WHERE luckydraw_id = '" . (int)$luckydraw_id . "'");
      foreach ($data['numbers'] as $number) {
        $number['luckydraw_id'] = $luckydraw_id;
        M('luckydraw_number')->data($number)->add();
      }
    }
    //如果是优惠券类型活动，生成优惠券
    return $luckydraw_id;
  }

  public function editLuckydraw($data) {
    $luckydraw_id = $data['luckydraw_id'];
    $this->db->query("UPDATE " . DB_PREFIX . "luckydraw SET luckydraw_name = '" . $this->db->escape($data['luckydraw_name']) . "', start_time = '" . $this->db->escape($data['start_time']) . "', end_time = '" . $this->db->escape($data['end_time']) . "', chance = '" . (float)$data['chance'] . "', status = '" . (int)$data['status'] . "' WHERE luckydraw_id='" . (int)$luckydraw_id . "'");

    if (isset($data['products'])) {
      foreach ($data['products'] as $product) {
        $this->db->query("UPDATE " . DB_PREFIX . "luckydraw_product SET quantity = '" . (int)$product['quantity'] . "' WHERE luckydraw_product_id='" . (int)$product['luckydraw_product_id'] . "' AND luckydraw_id='" . (int)$luckydraw_id . "'");
      }
    }
    if (isset($data['numbers'])) {
      $this->db->query("DELETE FROM " . DB_PREFIX . "luckydraw_number WHERE luckydraw_id = '" . (int)$luckydraw_id . "'");
      foreach ($data['numbers'] as $number) {
        $number['luckydraw_id'] = $luckydraw_id;
        M('luckydraw_number')->data($number)->add();
      }
    }
  }

  public function activateLuckydraw($luckydraw_id) {
    $this->db->query("UPDATE " . DB_PREFIX . "luckydraw SET status = 1 WHERE luckydraw_id='" . (int)$luckydraw_id . "' AND status = 0");
  }

  public function deleteLuckydraw($luckydraw_id) {
    $luckydraw = $this->getLuckydraw($luckydraw_id);
    if($luckydraw) {
      if($luckydraw['status']==0) {
        $this->db->query("DELETE FROM " . DB_PREFIX . "luckydraw WHERE luckydraw_id = '" . (int)$luckydraw_id . "' AND status = 0");
        $this->db->query("DELETE FROM " . DB_PREFIX . "luckydraw_product WHERE luckydraw_id = '" . (int)$luckydraw_id . "'");
        $this->db->query("DELETE FROM " . DB_PREFIX . "luckydraw_number WHERE luckydraw_id = '" . (int)$luckydraw_id . "'");
      }
    }
  }

  public function getLuckydraw($luckydraw_id) {
    $luckydraw_info = array();
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "luckydraw WHERE luckydraw_id = '" . (int)$luckydraw_id . "'");
    if($query->num_rows) {
      $luckydraw_info = $query->row;
      $luckydraw_product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "luckydraw_product WHERE luckydraw_id = '" . (int)$luckydraw_id . "' order by sort_order asc");
      $luckydraw_info['products'] = $luckydraw_product_query->rows;
      $luckydraw_number_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "luckydraw_number WHERE luckydraw_id = '" . (int)$luckydraw_id . "'");
      $luckydraw_info['numbers'] = $luckydraw_number_query->rows;

      $luckydraw_history_query = $this->db->query("SELECT luckydraw_product_id, SUM(hit) AS hit_number FROM " . DB_PREFIX . "luckydraw_history WHERE luckydraw_id = '" . (int)$luckydraw_id . "' GROUP BY luckydraw_product_id");
      $luckydraw_hits = $luckydraw_history_query->rows;
      $product_qty = array();
      foreach ($luckydraw_hits as $lh) {
        $product_qty[$lh['luckydraw_product_id']] = $lh['hit_number'];
      }
      foreach ($luckydraw_info['products'] as $key=>$lp) {
        $luckydraw_info['products'][$key]['hitted_number'] = $product_qty[$lp['luckydraw_product_id']]?$product_qty[$lp['luckydraw_product_id']]:0;
      }
      
    }

    return $luckydraw_info;
  }

  public function getLuckydraws($data = array()) {
    $sql = "SELECT * FROM " . DB_PREFIX . "luckydraw ";

    $sql .= " WHERE 1";
    
    if (!empty($data['filter_luckydraw_name'])) {
      $sql .= " AND luckydraw_name LIKE '%" . $this->db->escape($data['filter_luckydraw_name']) . "%'";
    }
    if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
      $sql .= " AND status = '" . (int)$data['filter_status'] . "'";
    }
    

    $sort_data = array(
      'luckydraw_id', 'luckydraw_name', 'status'
    );

    if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
      $sql .= " ORDER BY " . $data['sort'];
    } else {
      $sql .= " ORDER BY luckydraw_id";
    }

    if (isset($data['order']) && ($data['order'] == 'ASC')) {
      $sql .= " ASC";
    } else {
      $sql .= " DESC";
    }

    if (isset($data['start']) || isset($data['limit'])) {
      if ($data['start'] < 0) {
        $data['start'] = 0;
      }

      if ($data['limit'] < 1) {
        $data['limit'] = 20;
      }

      $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
    }

    $query = $this->db->query($sql);

    return $query->rows;
  }

  public function getLuckydrawProducts($luckydraw_id) {
    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "luckydraw_to_product WHERE luckydraw_id = (SELECT luckydraw_id FROM " . DB_PREFIX . "luckydraw WHERE luckydraw_id='" . (int)$luckydraw_id . "')");
    return $query->rows;
  }

  public function getLuckydrawProductsIds($luckydraw_id) {
    $luckydraw_product_data = array();

    $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "luckydraw_to_product WHERE luckydraw_id = (SELECT luckydraw_id FROM " . DB_PREFIX . "luckydraw WHERE luckydraw_id='" . (int)$luckydraw_id . "')");

    foreach ($query->rows as $result) {
      $luckydraw_product_data[] = $result['product_id'];
    }

    return $luckydraw_product_data;
  }

  public function getTotalLuckydraws($data = array()) {
    $sql = "SELECT COUNT(luckydraw_id) AS total FROM " . DB_PREFIX . "luckydraw ";

    $sql .= " WHERE 1";

    if (!empty($data['filter_luckydraw_name'])) {
      $sql .= " AND luckydraw_name LIKE '%" . $this->db->escape($data['filter_luckydraw_name']) . "%'";
    }

    if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
      $sql .= " AND status = '" . (int)$data['filter_status'] . "'";
    }

    $query = $this->db->query($sql);

    return $query->row['total'];
  }

  public function getScoupons($data = array()) {
    $sql = "SELECT sts.luckydraw_to_scoupon_id, s.luckydraw_name, sts.scoupon_code FROM " . DB_PREFIX . "luckydraw_to_scoupon sts LEFT JOIN " . DB_PREFIX . "luckydraw s ON sts.luckydraw_id=s.luckydraw_id" ;

    $sql .= " WHERE 1";
    
    if (!empty($data['filter_luckydraw_name'])) {
      $sql .= " AND s.luckydraw_name LIKE '%" . $this->db->escape($data['filter_luckydraw_name']) . "%'";
    }

    $sort_data = array(
      's.luckydraw_name', 'sts.luckydraw_to_scoupon_id'
    );

    if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
      $sql .= " ORDER BY " . $data['sort'];
    } else {
      $sql .= " ORDER BY sts.luckydraw_to_scoupon_id";
    }

    if (isset($data['order']) && ($data['order'] == 'ASC')) {
      $sql .= " ASC";
    } else {
      $sql .= " DESC";
    }

    if (isset($data['start']) || isset($data['limit'])) {
      if ($data['start'] < 0) {
        $data['start'] = 0;
      }

      if ($data['limit'] < 1) {
        $data['limit'] = 20;
      }

      $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
    }

    $query = $this->db->query($sql);

    return $query->rows;
  }

  public function getTotalScoupons($data = array()) {
    $sql = "SELECT COUNT(sts.luckydraw_to_scoupon_id) AS total FROM " . DB_PREFIX . "luckydraw_to_scoupon sts LEFT JOIN " . DB_PREFIX . "luckydraw s ON sts.luckydraw_id=s.luckydraw_id" ;

    $sql .= " WHERE 1";

    if (!empty($data['filter_luckydraw_name'])) {
      $sql .= " AND s.luckydraw_name LIKE '%" . $this->db->escape($data['filter_luckydraw_name']) . "%'";
    }

    if (!empty($data['filter_luckydraw_id'])) {
      $sql .= " AND s.luckydraw_id = '" . (int)$data['filter_luckydraw_id'] . "'";
    }

    $query = $this->db->query($sql);

    return $query->row['total'];
  }

  public function addScoupon($data) {
    //生成amount条giftcard
    $count = $data['scoupon_number'];
    $luckydraw_id = $data['luckydraw_id'];
    for ($i = 0; $i < $count; $i++) {
      $scoupon_code = substr(md5(uniqid(rand(), true)), 0, 8);
      $this->db->query("INSERT INTO " . DB_PREFIX . "luckydraw_to_scoupon SET scoupon_code = '" . $scoupon_code . "', luckydraw_id = '" . $luckydraw_id . "'");
    }
  }

  public function deleteScoupon($luckydraw_to_scoupon_id) {
    $this->db->query("DELETE FROM " . DB_PREFIX . "luckydraw_to_scoupon WHERE luckydraw_to_scoupon_id = '" . (int)$luckydraw_to_scoupon_id . "'");
  }

  public function isUrlCodeUnique($url_code, $luckydraw_id=0) {
    $sql = "SELECT * FROM " . DB_PREFIX . "luckydraw WHERE url_code = '" . $this->db->escape($url_code) . "'";
    if($luckydraw_id!=0) {
      $sql .= " AND luckydraw_id!='" . $luckydraw_id . "'";
    }
    $query = $this->db->query($sql);
    if($query->num_rows) {
      return false;
    }
    return true;
  }

  public function getSoldTotalOrder($luckydraw_id) {
    $sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "luckydraw_buy_history WHERE luckydraw_id = '" . (int)$luckydraw_id . "'";
    $query = $this->db->query($sql);

    return $query->row['total'];
  }

  public function getProductSoldNumber($luckydraw_id, $product_id) {
    $query = $this->db->query("SELECT SUM(discount_sold_number) AS total FROM " . DB_PREFIX . "luckydraw_product_stock WHERE luckydraw_id = '" . (int)$luckydraw_id . "' AND product_id = '" . (int)$product_id . "'");
    
    return is_null($query->row['total'])?0:$query->row['total'];
  }

  public function getValidateMethodMap() {
    return array('0'=>'员工卡号认证',
      '1'=>'优惠码认证',
      '2'=>'电气员工卡号认证',
      '3'=>'总工会认证');
  }

  public function getCompanyTitleMap() {
    return array('0'=>'不需要',
      '1'=>'公司',
      '2'=>'部门');
  }

  public function getLuckydrawStatusMap() {
    return array('0'=>'未激活',
      '1'=>'已激活',
      '2'=>'已关闭');
  }

  public function getAvaliablePaymentMethods() {
    return array('wxpay'=>'微信公众号支付',
      'qrcode_wxpay'=>'微信二维码支付');
  }

  public function getExportHitRecords($luckydraw_id) {
    $luckydraw_history_model = M('luckydraw_history');
    $map['lh.luckydraw_id'] = array('eq',$luckydraw_id);
    $map['lh.hit'] = array('eq',1);
    $luckydraw_history_data = $luckydraw_history_model
    ->alias('lh')
    ->join('LEFT JOIN `luckydraw_product` lp ON lp.luckydraw_product_id=lh.luckydraw_product_id')
    ->join('LEFT JOIN `luckydraw` l ON l.luckydraw_id=lh.luckydraw_id')
    ->join('LEFT JOIN `order` o ON o.order_id=lh.order_id')
	->join('LEFT JOIN `user` u ON o.recommend_usr_id=u.user_id')
    ->order('lh.luckydraw_history_id')
    ->where($map)
	->field('lh.*,lp.*,l.*,o.*,u.*,lh.date_added as ldate')
    ->select(); 
    return $luckydraw_history_data;
  }
  public function getLuckydrawBuyHistory($filter){
      if(isset($filter['filter_order_status_id'])){
         $map['o.order_status_id'] = $filter['filter_order_status_id'];
      }else{
         $map['o.order_status_id'] = '-1';
      }
      if(isset($filter['luckydraw_id'])){
        $map['sbh.luckydraw_id'] = $filter['luckydraw_id'];
      }else{
        $map['sbh.luckydraw_id'] = 0;
      }
      if(isset($filter['order'])){
        $order = $order.' ASC';
      }else{
        $order = 'o.order_id ASC';
      }
      $luckydraw_buy_history_model = M('luckydraw_buy_history');
      $luckydraws_data = $luckydraw_buy_history_model
      ->alias('sbh')
      ->join('LEFT JOIN `order` o ON o.order_id=sbh.order_id')
      ->where($map)
      ->field('o.order_id,sbh.luckydraw_id,o.shipping_fullname,o.shipping_telephone')
      ->select();
      return $luckydraws_data;
  }  
}